﻿using Dapper;
using Helper;
using IDAL;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class MsSqlDapperRepository : IDapperRepository
    {
        /// <summary>
        /// 默认链接字符串
        /// </summary>

        private static string DefaultDbConnectionString = @"server=localhost;port=3306;database=zyh_operation;user=root;password=a287572291;SslMode=None;";



        /// <summary>
        /// 链接字符串
        /// </summary>
        private static string connectionString;

        #region +获取连接

        /// <summary>
        /// 获取一个数据库连接对象
        /// </summary>
        /// <returns></returns>
        public IDbConnection GetConection()
        {

            IDbConnection conn = new SqlConnection(connectionString);
            if (conn.State == ConnectionState.Closed) conn.Open();
            return conn;
        }

        #endregion

        #region +BaseDAL构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="SqlConnection"></param>
        public MsSqlDapperRepository(string SqlConnectionString = null)
        {
            if (string.IsNullOrWhiteSpace(SqlConnectionString))
            {
                connectionString = DefaultDbConnectionString;
            }
            else
            {
                connectionString = SqlConnectionString;
            }
        }



        #endregion

        #region +插入实体

        /// <summary>
        /// 增加实体操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool InsertEntity<T>(string cmd, T entity) where T : class, new()
        {
            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                return conn.Execute(cmd, entity) > 0;
            }
        }



        #endregion

        #region +获取所有实体
        /// <summary>
        /// 获取所有实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public IEnumerable<T> RetriveAllEntity<T>(string cmd) where T : class, new()
        {
            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                return conn.Query<T>(cmd);
            }
        }
        #endregion

        #region +根据主键获取实体

        /// <summary>
        /// 获取单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public T GetEntityById<T>(string cmd, int id) where T : class, new()
        {
            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                return conn.QueryFirstOrDefault<T>(cmd, new { id = id });
            }
        }

        #endregion

        #region +更新实体

        /// <summary>
        /// 更新实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd"></param>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool UpdateEntity<T>(string cmd, T entity) where T : class, new()
        {
            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                return conn.Execute(cmd, entity) > 0;
            }
        }

        public bool LogicDeleteEntity<T>(string cmd, DynamicParameters param) where T : class, new()
        {
            return ExcuteNonQuery<T>(cmd, param) > 0;
        }

        #endregion

        #region +删除实体，根据主键
        /// <summary>
        /// 删除实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteEntityById<T>(string cmd, int id) where T : class, new()
        {
            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                return conn.Execute(cmd, new { id = id }) > 0;
            }
        }
        #endregion

        #region +ExcuteNonQuery 增、删、改同步操作
        /// <summary>
        /// 增、删、改同步操作
        ///  </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="connection">链接字符串</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>int</returns>
        public int ExcuteNonQuery<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
        {
            int result = 0;
            using (IDbConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                if (flag)
                {
                    result = con.Execute(cmd, param, null, null, CommandType.StoredProcedure);
                }
                else
                {
                    result = con.Execute(cmd, param, null, null, CommandType.Text);
                }
            }
            return result;
        }
        #endregion

        #region +ExcuteNonQueryAsync 增、删、改异步操作
        /// <summary>
        /// 增、删、改异步操作
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="connection">链接字符串</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>int</returns>
        public async Task<int> ExcuteNonQueryAsync<T>(string connection, string cmd, DynamicParameters param, bool flag = true) where T : class, new()
        {
            int result = 0;
            using (IDbConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                if (flag)
                {
                    result = await con.ExecuteAsync(cmd, param, null, null, CommandType.StoredProcedure);
                }
                else
                {
                    result = await con.ExecuteAsync(cmd, param, null, null, CommandType.Text);
                }
            }
            return result;
        }
        #endregion

        #region +ExecuteScalar 同步查询操作
        /// <summary>
        /// 同步查询操作
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="connection">连接字符串</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>object</returns>
        public object ExecuteScalar<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
        {

            object result = null;
            using (IDbConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                if (flag)
                {
                    result = con.ExecuteScalar(cmd, param, null, null, CommandType.StoredProcedure);
                }
                else
                {
                    result = con.ExecuteScalar(cmd, param, null, null, CommandType.Text);
                }
            }
            return result;
        }
        #endregion

        #region +ExecuteScalarAsync 异步查询操作
        /// <summary>
        /// 异步查询操作
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="connection">连接字符串</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>object</returns>
        public async Task<object> ExecuteScalarAsync<T>(string connection, string cmd, DynamicParameters param, bool flag = true) where T : class, new()
        {
            object result = null;
            using (IDbConnection con = new SqlConnection(connection))
            {
                con.Open();
                if (flag)
                {
                    result = await con.ExecuteScalarAsync(cmd, param, null, null, CommandType.StoredProcedure);
                }
                else
                {
                    result = con.ExecuteScalarAsync(cmd, param, null, null, CommandType.Text);
                }
            }
            return result;
        }
        #endregion

        #region +FindOne  同步查询一条数据
        /// <summary>
        /// 同步查询一条数据
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="connection">连接字符串</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>t</returns>
        public T FindOne<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
        {
            IDataReader dataReader = null;
            using (IDbConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                if (flag)
                {
                    dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.StoredProcedure);
                }
                else
                {
                    dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.Text);
                }
                if (dataReader == null || !dataReader.Read()) return null;
                Type type = typeof(T);
                T t = new T();
                foreach (var item in type.GetProperties())
                {
                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        //属性名与查询出来的列名比较
                        if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
                        var kvalue = dataReader[item.Name];
                        if (kvalue == DBNull.Value) continue;
                        item.SetValue(t, kvalue, null);
                        break;
                    }
                }
                return t;
            }
        }
        #endregion

        #region +FindOne  异步查询一条数据
        /// <summary>
        /// 异步查询一条数据
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>t</returns>
        public async Task<T> FindOneAsync<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
        {
            try
            {


                IDataReader dataReader = null;
                using (IDbConnection con = new SqlConnection(connectionString))
                {
                    con.Open();
                    if (flag)
                    {
                        dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.StoredProcedure);
                    }
                    else
                    {
                        dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.Text);
                    }

                    if (dataReader == null || !dataReader.Read()) return null;
                    Type type = typeof(T);
                    T t = new T();
                    foreach (var item in type.GetProperties())
                    {
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            //属性名与查询出来的列名比较
                            if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
                            var kvalue = dataReader[item.Name];
                            if (kvalue == DBNull.Value) continue;
                            item.SetValue(t, kvalue, null);
                            break;
                        }
                    }
                    return t;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion

        #region +FindToList  同步查询数据集合
        /// <summary>
        /// 同步查询数据集合
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="connection">连接字符串</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>t</returns>
        public List<T> FindToList<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
        {
            //MySqlDataReader
            IDataReader dataReader = null;
            using (IDbConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                if (flag)
                {
                    dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.StoredProcedure);
                }
                else
                {
                    dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.Text);
                }

                //if (dataReader == null || !dataReader) return null;

                Type type = typeof(T);
                List<T> tlist = new List<T>();
                while (dataReader.Read())
                {
                    T t = new T();
                    foreach (var item in type.GetProperties())
                    {
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            //属性名与查询出来的列名比较
                            if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
                            var kvalue = dataReader[item.Name];
                            if (kvalue == DBNull.Value) continue;
                            item.SetValue(t, kvalue, null);
                            break;
                        }
                    }
                    if (tlist != null) tlist.Add(t);
                }
                return tlist;
            }
        }
        #endregion

        #region +FindToListAsync  异步查询数据集合
        /// <summary>
        /// 异步查询数据集合
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="connection">连接字符串</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>t</returns>
        public async Task<IList<T>> FindToListAsync<T>(string cmd, DynamicParameters param, bool flag = true) where T : class, new()
        {
            //IDataReader dataReader = null;
            using (IDbConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                CommandType ct = CommandType.Text;
                if (flag)
                {
                    ct = CommandType.StoredProcedure;
                    //dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.StoredProcedure);
                }
                else
                {
                    ct = CommandType.Text;
                    //dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.Text);
                }

                using (var dataReader = (DbDataReader)await con.ExecuteReaderAsync(cmd, param, null, null, ct))
                {
                    Type type = typeof(T);
                    List<T> tlist = new List<T>();
                    while (await dataReader.ReadAsync())
                    {
                        T t = new T();
                        foreach (var item in type.GetProperties())
                        {
                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                //属性名与查询出来的列名比较
                                if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
                                var kvalue = dataReader[item.Name];
                                if (kvalue == DBNull.Value) continue;
                                item.SetValue(t, kvalue, null);
                                break;
                            }
                        }
                        if (tlist != null) tlist.Add(t);
                    }
                    return tlist;
                }

            }
        }
        #endregion

        #region +FindToList  同步查询数据集合
        /// <summary>
        /// 同步查询数据集合
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="connection">连接字符串</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>t</returns>
        public IList<T> FindToListAsPage<T>(string connection, string cmd, DynamicParameters param, bool flag = true) where T : class, new()
        {
            IDataReader dataReader = null;
            using (IDbConnection con = new SqlConnection(connection))
            {
                con.Open();
                if (flag)
                {
                    dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.StoredProcedure);
                }
                else
                {
                    dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.Text);
                }
                if (dataReader == null || !dataReader.Read()) return null;
                Type type = typeof(T);
                List<T> tlist = new List<T>();
                while (dataReader.Read())
                {
                    T t = new T();
                    foreach (var item in type.GetProperties())
                    {
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            //属性名与查询出来的列名比较
                            if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
                            var kvalue = dataReader[item.Name];
                            if (kvalue == DBNull.Value) continue;
                            item.SetValue(t, kvalue, null);
                            break;
                        }
                    }
                    if (tlist != null) tlist.Add(t);
                }
                return tlist;
            }
        }
        #endregion

        #region +FindToListByPage  同步分页查询数据集合
        /// <summary>
        /// 同步分页查询数据集合
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="connection">连接字符串</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>t</returns>
        public IList<T> FindToListByPage<T>(string connection, string cmd, DynamicParameters param, bool flag = true) where T : class, new()
        {
            IDataReader dataReader = null;
            using (IDbConnection con = new SqlConnection(connection))
            {
                con.Open();
                if (flag)
                {
                    dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.StoredProcedure);
                }
                else
                {
                    dataReader = con.ExecuteReader(cmd, param, null, null, CommandType.Text);
                }
                if (dataReader == null || !dataReader.Read()) return null;
                Type type = typeof(T);
                List<T> tlist = new List<T>();
                while (dataReader.Read())
                {
                    T t = new T();
                    foreach (var item in type.GetProperties())
                    {
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            //属性名与查询出来的列名比较
                            if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
                            var kvalue = dataReader[item.Name];
                            if (kvalue == DBNull.Value) continue;
                            item.SetValue(t, kvalue, null);
                            break;
                        }
                    }
                    if (tlist != null) tlist.Add(t);
                }
                return tlist;
            }
        }
        #endregion

        #region +FindToListByPageAsync  异步分页查询数据集合
        /// <summary>
        /// 异步分页查询数据集合
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="connection">连接字符串</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>t</returns>
        public async Task<IList<T>> FindToListByPageAsync<T>(string connection, string cmd, DynamicParameters param, bool flag = true) where T : class, new()
        {
            IDataReader dataReader = null;
            using (IDbConnection con = new SqlConnection(connection))
            {
                con.Open();
                if (flag)
                {
                    dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.StoredProcedure);
                }
                else
                {
                    dataReader = await con.ExecuteReaderAsync(cmd, param, null, null, CommandType.Text);
                }
                if (dataReader == null || !dataReader.Read()) return null;
                Type type = typeof(T);
                List<T> tlist = new List<T>();
                while (dataReader.Read())
                {
                    T t = new T();
                    foreach (var item in type.GetProperties())
                    {
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            //属性名与查询出来的列名比较
                            if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;
                            var kvalue = dataReader[item.Name];
                            if (kvalue == DBNull.Value) continue;
                            item.SetValue(t, kvalue, null);
                            break;
                        }
                    }
                    if (tlist != null) tlist.Add(t);
                }
                return tlist;
            }
        }
        #endregion

        #region +通用查询方法(主要用于分页存储过程)

        /// <summary>
        /// 通用查询方法
        /// </summary>
        /// <param name="search">查询条件</param>
        /// <returns>数据集合</returns>
        public DataTable FindTable(PagerInfo info, ref int recordCount, ref int pageCount)
        {
            string where = string.Empty;
            if (info.Condition != null && info.Condition.ConditionTable != null && info.Condition.ConditionTable.Count > 0)
            {
                where = BuildConditionSql(info.Condition.ConditionTable);
            }
            else
            {
                where = info.Where == null ? null : info.Where;
            }

            
            SqlParameter[] para = new SqlParameter[]{
                            new SqlParameter("@p_cloumns",info.Fields),
                            new SqlParameter("@p_tables",info.TableName),
                            new SqlParameter("@p_where",where),
                            new SqlParameter("@p_order",info.SortField),
                            new SqlParameter("@p_pageindex",info.CurPage),
                            new SqlParameter("@p_pagesize",info.PageSize),
                            new SqlParameter("@p_recordcount",SqlDbType.Int,5,ParameterDirection.Output,false,0,0,string.Empty,DataRowVersion.Original,null),
                            new SqlParameter("@p_pagecount",SqlDbType.Int,5,ParameterDirection.Output,false,0,0,string.Empty,DataRowVersion.Original,null)
                };
            DataSet ds = RunProcedure("proc_pagination", para, "pageTable");

            recordCount = Strings.ToInt(para[6].Value.ToString());
            pageCount = Strings.ToInt(para[7].Value.ToString());
            return ds.Tables[0];
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                DataSet dataset = new DataSet();
                conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = BuildQueryCommand(conn, storedProcName, parameters);
                adapter.Fill(dataset, tableName);
                conn.Close();
                return dataset;
            }
        }

        /// <summary>
        /// 构建 MysqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>OracleCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }


        #endregion

        #region SearchCondition

        #region BuildSql
        /// <summary>
        /// 根据对象构造相关的条件语句（不使用参数），如返回的语句是:
        /// <![CDATA[
        /// Where (1=1)  AND Test4  <  'Value4' AND Test6  >=  'Value6' AND Test7  <=  'value7' AND Test  <>  '1' AND Test5  >  'Value5' AND Test2  Like  '%Value2%' AND Test3  =  'Value3'
        /// ]]>
        /// </summary>
        /// <returns></returns> 
        public string BuildConditionSql(Hashtable conditionTable)
        {
            if (conditionTable == null || conditionTable.Values == null || conditionTable.Values.Count < 1)
            {
                return "";
            }

            string sql = string.Empty;
            SearchInfo searchInfo = null;

            StringBuilder sb = new StringBuilder();
            sql += BuildGroupCondiction(conditionTable);

            foreach (DictionaryEntry de in conditionTable)
            {
                searchInfo = (SearchInfo)de.Value;

                //如果选择ExcludeIfEmpty为True,并且该字段为空值的话,跳过
                if (searchInfo.ExcludeIfEmpty &&
                    (searchInfo.FieldValue == null || string.IsNullOrEmpty(searchInfo.FieldValue.ToString())))
                {
                    continue;
                }

                //只有组别名称为空才继续，即正常的sql条件
                if (string.IsNullOrEmpty(searchInfo.GroupName))
                {
                    if (searchInfo.SqlOperator == SqlOperator.Like)
                    {
                        sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                            this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", Strings.Trim(searchInfo.FieldValue)));
                    }
                    else if (searchInfo.SqlOperator == SqlOperator.NotLike)
                    {
                        sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                            this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", Strings.Trim(searchInfo.FieldValue)));
                    }
                    else if (searchInfo.SqlOperator == SqlOperator.LikeStartAt)
                    {
                        sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                            this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("{0}%", Strings.Trim(searchInfo.FieldValue)));
                    }
                    else if (searchInfo.SqlOperator == SqlOperator.LikeEndAt)
                    {
                        sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                            this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}", Strings.Trim(searchInfo.FieldValue)));
                    }
                    else if (searchInfo.SqlOperator == SqlOperator.In)
                    {
                        sb.AppendFormat(" AND {0} {1} {2}", searchInfo.FieldName,
                            this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("({0})", Strings.Trim(searchInfo.FieldValue)));
                    }
                    else
                    {

                        #region 特殊Oracle操作
                        if (searchInfo.FieldValue != null && IsDate(searchInfo.FieldValue.ToString()))
                        {
                            sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                                this.ConvertSqlOperator(searchInfo.SqlOperator), Strings.Trim(searchInfo.FieldValue));
                        }
                        else if (searchInfo.FieldValue != null && IsDateHourMinute(searchInfo.FieldValue.ToString()))
                        {
                            sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                                this.ConvertSqlOperator(searchInfo.SqlOperator), Strings.Trim(searchInfo.FieldValue));
                        }
                        else if (!searchInfo.ExcludeIfEmpty)
                        {
                            //如果要进行空值查询的时候
                            if (searchInfo.SqlOperator == SqlOperator.Equal)
                            {
                                sb.AppendFormat(" AND ({0} is null or {0}='')", Strings.Trim(searchInfo.FieldName));
                            }
                            else if (searchInfo.SqlOperator == SqlOperator.NotEqual)
                            {
                                sb.AppendFormat(" AND {0} is not null", Strings.Trim(searchInfo.FieldName));
                            }
                        }
                        else
                        {
                            sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                                this.ConvertSqlOperator(searchInfo.SqlOperator), Strings.Trim(searchInfo.FieldValue));
                        }
                        #endregion

                        #region 特殊Oracle操作
                        //if (searchInfo.FieldValue != null && IsDate(searchInfo.FieldValue.ToString()))
                        //{
                        //    sb.AppendFormat(" AND {0} {1} to_date('{2}','YYYY-MM-dd')", searchInfo.FieldName,
                        //        this.ConvertSqlOperator(searchInfo.SqlOperator), Strings.Trim(searchInfo.FieldValue));
                        //}
                        //else if (searchInfo.FieldValue != null && IsDateHourMinute(searchInfo.FieldValue.ToString()))
                        //{
                        //    sb.AppendFormat(" AND {0} {1} to_date('{2}','YYYY-MM-dd hh24:mi:ss')", searchInfo.FieldName,
                        //        this.ConvertSqlOperator(searchInfo.SqlOperator), Strings.Trim(searchInfo.FieldValue));
                        //}
                        //else if (!searchInfo.ExcludeIfEmpty)
                        //{
                        //    //如果要进行空值查询的时候
                        //    if (searchInfo.SqlOperator == SqlOperator.Equal)
                        //    {
                        //        sb.AppendFormat(" AND ({0} is null or {0}='')", Strings.Trim(searchInfo.FieldName));
                        //    }
                        //    else if (searchInfo.SqlOperator == SqlOperator.NotEqual)
                        //    {
                        //        sb.AppendFormat(" AND {0} is not null", Strings.Trim(searchInfo.FieldName));
                        //    }
                        //}
                        //else
                        //{
                        //    sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,
                        //        this.ConvertSqlOperator(searchInfo.SqlOperator), Strings.Trim(searchInfo.FieldValue));
                        //}
                        #endregion
                    }
                }
            }

            sql += sb.ToString();
            if (!string.IsNullOrEmpty(sql) && sql.Length > 4) sql = sql.Remove(0, 4);

            return sql;
        }

        /// <summary>
        /// 建立分组条件
        /// </summary>
        /// <returns></returns>
        private string BuildGroupCondiction(Hashtable conditionTable)
        {
            Hashtable ht = GetGroupNames(conditionTable);
            SearchInfo searchInfo = null;
            StringBuilder sb = new StringBuilder();
            string sql = string.Empty;
            string tempSql = string.Empty;

            foreach (string groupName in ht.Keys)
            {
                sb = new StringBuilder();
                tempSql = " AND ({0})";
                foreach (DictionaryEntry de in conditionTable)
                {
                    searchInfo = (SearchInfo)de.Value;

                    //如果选择ExcludeIfEmpty为True,并且该字段为空值的话,跳过
                    if (searchInfo.ExcludeIfEmpty &&
                        (searchInfo.FieldValue == null || string.IsNullOrEmpty(searchInfo.FieldValue.ToString())))
                    {
                        continue;
                    }

                    if (groupName.Equals(searchInfo.GroupName, StringComparison.OrdinalIgnoreCase))
                    {
                        if (searchInfo.SqlOperator == SqlOperator.Like)
                        {
                            sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", Strings.Trim(searchInfo.FieldValue)));
                        }
                        else if (searchInfo.SqlOperator == SqlOperator.NotLike)
                        {
                            sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", Strings.Trim(searchInfo.FieldValue)));
                        }
                        else if (searchInfo.SqlOperator == SqlOperator.LikeStartAt)
                        {
                            sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("{0}%", Strings.Trim(searchInfo.FieldValue)));
                        }
                        else if (searchInfo.SqlOperator == SqlOperator.LikeEndAt)
                        {
                            sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}", Strings.Trim(searchInfo.FieldValue)));
                        }
                        else if (searchInfo.SqlOperator == SqlOperator.In)
                        {
                            sb.AppendFormat(" OR {0} {1} {2}", searchInfo.FieldName,
                                this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("({0})", Strings.Trim(searchInfo.FieldValue)));
                        }
                        else
                        {
                            #region Oracle分组
                            if (searchInfo.FieldValue != null && IsDate(searchInfo.FieldValue.ToString()))
                            {
                                sb.AppendFormat(" OR {0} {1} to_date('{2}','YYYY-MM-dd')", searchInfo.FieldName,
                                this.ConvertSqlOperator(searchInfo.SqlOperator), Strings.Trim(searchInfo.FieldValue));
                            }
                            else if (searchInfo.FieldValue != null && IsDateHourMinute(searchInfo.FieldValue.ToString()))
                            {
                                sb.AppendFormat(" OR {0} {1} to_date('{2}','YYYY-MM-dd HH:mi')", searchInfo.FieldName,
                                this.ConvertSqlOperator(searchInfo.SqlOperator), Strings.Trim(searchInfo.FieldValue));
                            }
                            else if (!searchInfo.ExcludeIfEmpty)
                            {
                                //如果要进行空值查询的时候
                                if (searchInfo.SqlOperator == SqlOperator.Equal)
                                {
                                    sb.AppendFormat(" OR ({0} is null or {0}='')", searchInfo.FieldName);
                                }
                                else if (searchInfo.SqlOperator == SqlOperator.NotEqual)
                                {
                                    sb.AppendFormat(" OR {0} is not null", searchInfo.FieldName);
                                }
                            }
                            else
                            {
                                sb.AppendFormat(" OR {0} {1} '{2}'", searchInfo.FieldName,
                                    this.ConvertSqlOperator(searchInfo.SqlOperator), Strings.Trim(searchInfo.FieldValue));
                            }
                            #endregion

                        }
                    }
                }

                if (!string.IsNullOrEmpty(sb.ToString()))
                {
                    tempSql = string.Format(tempSql, sb.ToString().Substring(4));//从第一个Or开始位置
                    sql += tempSql;
                }
            }

            return sql;
        }

        /// <summary>
        /// 获取给定条件集合的组别对象集合
        /// </summary>
        /// <returns></returns>
        private Hashtable GetGroupNames(Hashtable conditionTable)
        {
            Hashtable htGroupNames = new Hashtable();
            SearchInfo searchInfo = null;
            foreach (DictionaryEntry de in conditionTable)
            {
                searchInfo = (SearchInfo)de.Value;
                if (!string.IsNullOrEmpty(searchInfo.GroupName) && !htGroupNames.Contains(searchInfo.GroupName))
                {
                    htGroupNames.Add(searchInfo.GroupName, searchInfo.GroupName);
                }
            }

            return htGroupNames;
        }
        #endregion

        #region 辅助函数

        /// <summary>
        /// 转换枚举类型为对应的Sql语句操作符号
        /// </summary>
        /// <param name="sqlOperator">SqlOperator枚举对象</param>
        /// <returns><![CDATA[对应的Sql语句操作符号（如 ">" "<>" ">=")]]></returns>
        private string ConvertSqlOperator(SqlOperator sqlOperator)
        {
            string stringOperator = " = ";
            switch (sqlOperator)
            {
                case SqlOperator.Equal:
                    stringOperator = " = ";
                    break;
                case SqlOperator.LessThan:
                    stringOperator = " < ";
                    break;
                case SqlOperator.LessThanOrEqual:
                    stringOperator = " <= ";
                    break;
                case SqlOperator.Like:
                    stringOperator = " Like ";
                    break;
                case SqlOperator.NotLike:
                    stringOperator = " NOT Like ";
                    break;
                case SqlOperator.LikeStartAt:
                    stringOperator = " Like ";
                    break;
                case SqlOperator.LikeEndAt:
                    stringOperator = " Like ";
                    break;
                case SqlOperator.MoreThan:
                    stringOperator = " > ";
                    break;
                case SqlOperator.MoreThanOrEqual:
                    stringOperator = " >= ";
                    break;
                case SqlOperator.NotEqual:
                    stringOperator = " <> ";
                    break;
                case SqlOperator.In:
                    stringOperator = " in ";
                    break;
                default:
                    break;
            }

            return stringOperator;
        }

        /// <summary>
        /// 根据传入对象的值类型获取其对应的DbType类型
        /// </summary>
        /// <param name="fieldValue">对象的值</param>
        /// <returns>DbType类型</returns>
        private DbType GetFieldDbType(object fieldValue)
        {
            DbType type = DbType.String;

            switch (fieldValue.GetType().ToString())
            {
                case "System.Int16":
                    type = DbType.Int16;
                    break;
                case "System.UInt16":
                    type = DbType.UInt16;
                    break;
                case "System.Single":
                    type = DbType.Single;
                    break;
                case "System.UInt32":
                    type = DbType.UInt32;
                    break;
                case "System.Int32":
                    type = DbType.Int32;
                    break;
                case "System.UInt64":
                    type = DbType.UInt64;
                    break;
                case "System.Int64":
                    type = DbType.Int64;
                    break;
                case "System.String":
                    type = DbType.String;
                    break;
                case "System.Double":
                    type = DbType.Double;
                    break;
                case "System.Decimal":
                    type = DbType.Decimal;
                    break;
                case "System.Byte":
                    type = DbType.Byte;
                    break;
                case "System.Boolean":
                    type = DbType.Boolean;
                    break;
                case "System.DateTime":
                    type = DbType.DateTime;
                    break;
                case "System.Guid":
                    type = DbType.Guid;
                    break;
                default:
                    break;
            }
            return type;
        }

        /// <summary>
        /// 判断输入的字符是否为日期
        /// </summary>
        /// <param name="strValue"></param>
        /// <returns></returns>
        internal bool IsDate(string strValue)
        {
            DateTime dt = DateTime.Now;
            if (DateTime.TryParse(strValue, out dt))
            {
                if (strValue.Contains(":"))
                    return false;
                return true;
            }
            return false;
            //return Regex.IsMatch(strValue, @"^((\d{2}(([02468][048])|([13579][26]))[\-\/\s]?((((0?[13578])|(1[02]))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\s]?((0?[1-9])|([1-2][0-9])))))|(\d{2}(([02468][1235679])|([13579][01345789]))[\-\/\s]?((((0?[13578])|(1[02]))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))");
        }

        /// <summary>
        /// 判断输入的字符是否为日期,如2004-07-12 14:25|||1900-01-01 00:00|||9999-12-31 23:59
        /// </summary>
        /// <param name="strValue"></param>
        /// <returns></returns>
        internal bool IsDateHourMinute(string strValue)
        {
            DateTime dt = DateTime.Now;
            if (DateTime.TryParse(strValue, out dt))
            {
                if (strValue.Contains(":"))
                    return true;
                return false;
            }
            return false;
            // return Regex.IsMatch(strValue, @"^(19[0-9]{2}|[2-9][0-9]{3})-((0(1|3|5|7|8)|10|12)-(0[1-9]|1[0-9]|2[0-9]|3[0-1])|(0(4|6|9)|11)-(0[1-9]|1[0-9]|2[0-9]|30)|(02)-(0[1-9]|1[0-9]|2[0-9]))\x20(0[0-9]|1[0-9]|2[0-3])(:[0-5][0-9]){1}$");
        }

        #endregion

        #region DataTableToList


        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="dt"></param>
        /// <param name="isNullReturnList"></param>
        /// <returns></returns>
        public virtual List<U> DataTableToList<U>(DataTable dt, bool isNullReturnList = false)
        {
            //确认参数有效
            if (dt == null || dt.Rows.Count <= 0)
            {
                if (isNullReturnList)
                {
                    return new List<U>();
                }
                else
                {
                    return null;
                }
            }

            List<U> list = new List<U>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                U _u = Activator.CreateInstance<U>();
                //获取对象所有属性
                PropertyInfo[] propertyInfo = _u.GetType().GetProperties();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    foreach (PropertyInfo info in propertyInfo)
                    {
                        //属性名称和列名相同时赋值
                        if (dt.Columns[j].ColumnName.ToUpper().Equals(info.Name.ToUpper()))
                        {
                            if (dt.Rows[i][j] != DBNull.Value)
                            {
                                info.SetValue(_u, dt.Rows[i][j], null);
                            }
                            else
                            {
                                info.SetValue(_u, null, null);
                            }
                            break;
                        }
                    }
                }
                list.Add(_u);
            }
            return list;
        }
        #endregion

    }
    #endregion
}
